検証: SQL ServerでPARTITION BYの例文を試してみる

一時テーブルで確認してみる

code:sql

-- drop table #Shohin;

create table #Shohin(

商品id varchar (4)

, 商品名 varchar (20)

, 商品分類 varchar (20)

, 販売単価 int

, 仕入単価 int

, 登録日 varchar (10)

);

-- truncate table #Shohin;

insert

into #Shohin

values ('0001', 'Tシャツ', '衣服', 1000, 500, '2009-09-20')

, ('0002', '穴あけパンチ', '事務用品', 500, 320, '2009-09-11')

, ('0003', 'カッターシャツ', '衣服', 4000, 2800, null)

, ('0004', '包丁', 'キッチン用品', 3000, 2800, '2009-09-20')

, ('0005', '圧力鍋', 'キッチン用品', 6800, 5000, '2009-01-15')

, ('0006', 'フォーク', 'キッチン用品', 500, null, '2009-09-20')

, ('0007', 'おろしがね', 'キッチン用品', 880, 790, '2008-04-28')

, ('0008', 'ボールペン', '事務用品', 100, null, '2009-11-11');

select * from #Shohin;

table:result

商品id 商品名商品分類販売単価仕入単価登録日

0001 Tシャツ衣服 1000 500 2009-09-20

0002 穴あけパンチ事務用品 500 320 2009-09-11

0003 カッターシャツ衣服 4000 2800 « NULL »

0004 包丁キッチン用品 3000 2800 2009-09-20

0005 圧力鍋キッチン用品 6800 5000 2009-01-15

0006 フォークキッチン用品 500 « NULL » 2009-09-20

0007 おろしがねキッチン用品 880 790 2008-04-28

0008 ボールペン事務用品 100 « NULL » 2009-11-11

上記のようなテーブルに対してPARTITION BYで部分集合を作ってみる

「商品分類」ごとに「販売単価」の昇順でならべるには…

rank(), dense_rank(), row_number()

code:sql

select *,

rank() over (partition by 商品分類 order by 販売単価 asc) as ランキング

from #Shohin;

table:result

商品id 商品名商品分類販売単価仕入単価登録日ランキング (部分集合)

0006 フォークキッチン用品 500 « NULL » 2009-09-20 1 ┓

0007 おろしがねキッチン用品 880 790 2008-04-28 2 ┃

0004 包丁キッチン用品 3000 2800 2009-09-20 3 ┃

0005 圧力鍋キッチン用品 6800 5000 2009-01-15 4 ┛

0001 Tシャツ衣服 1000 500 2009-09-20 1 ┓

0003 カッターシャツ衣服 4000 2800 « NULL » 2 ┛

0008 ボールペン事務用品 100 « NULL » 2009-11-11 1 ┓

0002 穴あけパンチ事務用品 500 320 2009-09-11 2 ┛

集約関数を使い、「商品分類」ごとの合計金額をとってみる

自分のレコード(カレントレコード)を基準に計算しているのがわかる

code:sql

select *,

sum(販売単価) over (partition by 商品分類 order by 販売単価 asc) as 合計金額

from #Shohin;

table:result

商品id 商品名商品分類販売単価仕入単価登録日合計金額

0006 フォークキッチン用品 500 « NULL » 2009-09-20 500

0007 おろしがねキッチン用品 880 790 2008-04-28 1380

0004 包丁キッチン用品 3000 2800 2009-09-20 4380

0005 圧力鍋キッチン用品 6800 5000 2009-01-15 11180

0001 Tシャツ衣服 1000 500 2009-09-20 1000

0003 カッターシャツ衣服 4000 2800 « NULL » 5000

0008 ボールペン事務用品 100 « NULL » 2009-11-11 100

0002 穴あけパンチ事務用品 500 320 2009-09-11 600

「商品id」の移動平均をとってみる

rows 2 preceding: 2行前まで

rows 2 following: 2行後まで